package fr.enseirb.mycub.db;

import java.util.ArrayList;
import org.json.JSONException;

import fr.enseirb.mycub.db.structure.LineDAO;
import fr.enseirb.mycub.db.structure.SpecificLineDAO;
import fr.enseirb.mycub.db.structure.tram.TramwayStationDAO;
import android.database.DatabaseUtils.InsertHelper;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteStatement;
import android.provider.BaseColumns;
import android.util.Log;

public class TramwayDBHelper {

	// insertion des stations
	public static void insertTramwayStations(SQLiteDatabase mDb,
			ArrayList<TramwayStationDAO> jsonTramway) throws JSONException,
			SQLiteException {
		InsertHelper stationsInsertHelper = new InsertHelper(mDb,
				TramwayDBConstants.TRAMWAY_STATION_TABLE);
		final int idColumn = stationsInsertHelper
				.getColumnIndex(BaseColumns._ID);
		final int nameColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_NAME);
		final int openColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_OPEN);
		final int addressColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_ADDRESS);
		final int latitudeColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_LATITUDE);
		final int longitudeColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_LONGITUDE);
		final int favoriteColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_FAVORITE);
		final int size = jsonTramway.size();		
		final String sql = "INSERT INTO "
				+ TramwayDBConstants.TRAMWAY_STATION_TABLE
				+ " VALUES (?,?,?,?,?,?,?);";

		stationsInsertHelper.close();

		// insertion dans la table de liaision entre les stations de tramways et les specificlines
		InsertHelper linkInsertHelper = new InsertHelper(mDb,
				TramwayDBConstants.LINK_LINE_STATION_TABLE);
		final int stationIdColumn = linkInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_STATION_ID);
		final int specificLineIdColumn = linkInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_SPECIFIC_LINE_ID);
		final String sql2 = "INSERT INTO "
				+ TramwayDBConstants.LINK_LINE_STATION_TABLE
				+ " VALUES (?,?);";

		linkInsertHelper.close();


		//Log.i("Insert Station", "init des colonnes...");
		try {
			mDb.beginTransaction();
			SQLiteStatement insert = mDb.compileStatement(sql);
			int id;
			String name;
			//Log.i("Insert Station", "debut de la boucle... ( taille : "+size+" ) ");
			for (int i = 0; i < size; i++) {
				TramwayStationDAO jsonStation = jsonTramway.get(i);
				id = jsonStation.getId();
				name = jsonStation.getName();
				insert.bindLong(idColumn, id);
				insert.bindString(nameColumn, name);
				insert.bindLong(openColumn, jsonStation.isOpen() ? 1 : 0);
				insert.bindString(addressColumn, jsonStation.getAddress());
				insert.bindLong(latitudeColumn,
						(int) (jsonStation.getmLatitude() * 1E6));
				insert.bindLong(longitudeColumn,
						(int) (jsonStation.getmLongitude() * 1E6));
				insert.bindLong(favoriteColumn, 0); // Favorite
				insert.executeInsert();

				int[] specificLine = jsonStation.getSpecificLineId();

				//Log.i("Insert LinkLineStation", "debut de la boucle...");

				for (int j = 0; j < jsonStation.getSpecificLineId().length; j++) {
					SQLiteStatement insert2 = mDb.compileStatement(sql2);
					insert2.bindLong(stationIdColumn, id);
					insert2.bindLong(specificLineIdColumn, specificLine[j]);
					insert2.executeInsert();
				}

			}
			mDb.setTransactionSuccessful();
		} catch (SQLException e) {
			throw e;
		} finally {
			mDb.endTransaction();
		}
	}

	public static boolean syncTramwayStations(SQLiteDatabase mDb, ArrayList<TramwayStationDAO> jsonTramway) throws SQLiteException,
	JSONException {
		return false;
	}

	public static void cleanAndInsertTramwayStations(SQLiteDatabase mDb,
			ArrayList<TramwayStationDAO> jsonTramway) throws JSONException {
		mDb.delete(TramwayDBConstants.TRAMWAY_STATION_TABLE, null, null);
		insertTramwayStations(mDb, jsonTramway);
	}

	// insertion des lines
	public static void insertTramwayLines(SQLiteDatabase mDb,
			ArrayList<LineDAO> jsonLine) throws JSONException,
			SQLiteException {
		InsertHelper stationsInsertHelper = new InsertHelper(mDb,
				TramwayDBConstants.LINE_TABLE);

		final int idColumn = stationsInsertHelper
				.getColumnIndex(BaseColumns._ID);
		final int nameColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_NAME);
		final int typeColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_TYPE);

		final int size = jsonLine.size();

		final String sql = "INSERT INTO "
				+ TramwayDBConstants.LINE_TABLE
				+ " VALUES (?,?,?);";

		stationsInsertHelper.close();
		//Log.i("Insert Line", "init des colonnes...");
		try {
			mDb.beginTransaction();
			SQLiteStatement insert = mDb.compileStatement(sql);
			int id;
			String name;
			//Log.i("Insert Line", "debut de la boucle...");
			for (int i = 0; i < size; i++) {
				LineDAO jsonStation = jsonLine.get(i);
				id = jsonStation.getId();
				name = jsonStation.getName();
				insert.bindLong(idColumn, id);
				insert.bindString(nameColumn, name);
				insert.bindLong(typeColumn, jsonStation.getType());
				insert.executeInsert();

			}
			mDb.setTransactionSuccessful();
		} catch (SQLException e) {
			throw e;
		} finally {
			mDb.endTransaction();
		}
	}

	public static boolean syncTramwayLines(SQLiteDatabase mDb, ArrayList<LineDAO> jsonTramway) throws SQLiteException,
	JSONException {
		return false;
	}

	public static void cleanAndInsertTramwayLines(SQLiteDatabase mDb,
			ArrayList<LineDAO> jsonLine) throws JSONException {
		mDb.delete(TramwayDBConstants.LINE_TABLE, null, null);
		insertTramwayLines(mDb, jsonLine);
	}

	// insertion des specific lines
	public static void insertTramwaySpecificLines(SQLiteDatabase mDb,
			ArrayList<SpecificLineDAO> jsonSpecificLine) throws JSONException,
			SQLiteException {
		InsertHelper stationsInsertHelper = new InsertHelper(mDb,
				TramwayDBConstants.SPECIFIC_LINE_TABLE);

		final int idColumn = stationsInsertHelper
				.getColumnIndex(BaseColumns._ID);
		final int nameColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_NAME);
		final int lineIdColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_LINE_ID);
		final int stationStartIdColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_STATION_START_ID);
		final int stationEndIdColumn = stationsInsertHelper
				.getColumnIndex(TramwayDBConstants.KEY_STATION_END_ID);

		final int size = jsonSpecificLine.size();

		final String sql = "INSERT INTO "
				+ TramwayDBConstants.SPECIFIC_LINE_TABLE
				+ " VALUES (?,?,?,?,?);";

		stationsInsertHelper.close();
		//Log.i("Insert SpecificLine", "init des colonnes...");
		try {
			mDb.beginTransaction();
			SQLiteStatement insert = mDb.compileStatement(sql);
			int id;
			String name;
			//Log.i("Insert SpecificLine", "debut de la boucle...");
			for (int i = 0; i < size; i++) {
				SpecificLineDAO jsonStation = jsonSpecificLine.get(i);
				id = jsonStation.getId();
				name = jsonStation.getName();
				insert.bindLong(idColumn, id);
				insert.bindString(nameColumn, name);
				insert.bindLong(lineIdColumn, jsonStation.getLineId());
				insert.bindLong(stationStartIdColumn, jsonStation.getStationStartId());
				insert.bindLong(stationEndIdColumn, jsonStation.getStationEndId());
				insert.executeInsert();

			}
			mDb.setTransactionSuccessful();
		} catch (SQLException e) {
			throw e;
		} finally {
			mDb.endTransaction();
		}
	}

	public static boolean syncTramwaySpecificLines(SQLiteDatabase mDb, ArrayList<SpecificLineDAO> jsonTramway) throws SQLiteException,
	JSONException {
		return false;
	}

	public static void cleanAndInsertTramwaySpecificLines(SQLiteDatabase mDb,
			ArrayList<SpecificLineDAO> jsonSpecificLine) throws JSONException {
		mDb.delete(TramwayDBConstants.SPECIFIC_LINE_TABLE, null, null);
		insertTramwaySpecificLines(mDb, jsonSpecificLine);
	}

	public static int getStationCount(SQLiteDatabase mDb) throws SQLException {
		Cursor cursor = mDb.rawQuery("SELECT COUNT(*) AS count FROM "
				+ TramwayDBConstants.TRAMWAY_STATION_TABLE,null);
		cursor.moveToNext();
		int count = cursor.getInt(0);
		cursor.close();
		return count;
	}

	public static ArrayList<Cursor> getFilteredStationsCursor(SQLiteDatabase mDb,String[] projection,
			String where,
			String orderBy) {
		String nWhere;
		nWhere = where;
		ArrayList<Cursor> cursors = new ArrayList<Cursor>();
		cursors.add( mDb.query(TramwayDBConstants.TRAMWAY_STATION_TABLE, projection, nWhere,
				null, null, null,
				orderBy));
		cursors.add( mDb.query(TramwayDBConstants.LINE_TABLE, null, null,
				null, null, null,
				null));
		cursors.add( mDb.query(TramwayDBConstants.SPECIFIC_LINE_TABLE, null, null,
				null, null, null,
				null));
		cursors.add( mDb.query(TramwayDBConstants.LINK_LINE_STATION_TABLE, null, null,
				null, null, null,
				null));
		return cursors;
	}
	
	
	public static Cursor getLinkTableCursor(SQLiteDatabase mDb,String[] projection,
			String where,
			String orderBy) {
		String nWhere;
		nWhere = where;
		Cursor cursor =  mDb.query(TramwayDBConstants.LINK_LINE_STATION_TABLE, projection, nWhere,
				null, null, null,
				null);
		return cursor;
	}
	
	public static Cursor getSpecificLinesCursor(SQLiteDatabase mDb,String[] projection,
			String where,
			String orderBy) {
		String nWhere;
		nWhere = where;
		Cursor cursor =  mDb.query(TramwayDBConstants.LINE_TABLE, projection, nWhere,
				null, null, null,
				null);
		return cursor;
	}
	
	
	public static Cursor getSpecificLineCursor(SQLiteDatabase mDb,String[] projection,
			String where,
			String orderBy) {
		String nWhere;
		nWhere = where;
		Cursor cursor =  mDb.query(TramwayDBConstants.SPECIFIC_LINE_TABLE, projection, nWhere,
				null, null, null,
				null);
		return cursor;
	}

	public static Cursor getStation(SQLiteDatabase mDb, int id, String[] columns) throws SQLException {
		Cursor cursor = mDb.query(true, TramwayDBConstants.TRAMWAY_STATION_TABLE, columns,
				BaseColumns._ID + " = ?",
				new String[] {
				String.valueOf(id) },
				null, null, null, null);
		if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
			throw new SQLException("No Station found with ID " + id);
		}
		return cursor;
	}
}
